package com.rongzhong.routing.check;

import java.io.File;
import java.util.Date;
import java.util.Map;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.rongzhong.routing.checkNode.QueryCacheNode;
import com.rongzhong.routing.hibernate.HibernateUtil;
import com.rongzhong.routing.main.RoutingInspecte;
import com.rongzhong.routing.util.Configuration;
import com.rongzhong.routing.util.PerformanceUtil;

/**
 * 
 * @author longxuegang@gmail.com
 * @version 1.0 2011-9-22
 * @since 1.0
 * @see
 */

public class QueryCacheCheck {
	private Configuration configure = Configuration.getInstance();
	private HibernateUtil hibernate = HibernateUtil.getInstance();
	private PerformanceUtil perfUtil = PerformanceUtil.getInstance();
	private final String sourcefile = configure.getExcel();
	private final int dbCheckNumber = perfUtil.getDbCheckNumber(); //3
	private static final Logger log = LoggerFactory.getLogger(QueryCacheCheck.class);
	
	public Map<Integer, Map<String,String>> getValuesMap(){
		return RoutingInspecte.getInstance().getValuesMap();
	}
	
	public Map<Integer, Map<String,String>> getVariablesMap(){
		return RoutingInspecte.getInstance().getVariablesMap();
	}
	
	public void checkOutQueryCache(){
		for (int dbIndex = 0; dbIndex < dbCheckNumber; dbIndex++) {
			QueryCacheNode bean = initQueryCacheNode(dbIndex);  
			Session session = null;
			Transaction tr =  null;
			try {
				session = hibernate.getStorageDBSession();
				tr = session.beginTransaction();
				session.saveOrUpdate(bean);
				operateExcel(bean);
				tr.commit();
			} catch (Exception e) {
				tr.rollback();
				log.error("initThread run into error.",e);
			}finally{
				hibernate.closeSession(session);
			}
		}
	}
	
	public Object selectID(QueryCacheNode bean){
		Session session = null;
		try {
			session = hibernate.getStorageDBSession();
			SQLQuery query = session.createSQLQuery("select ID from QUERYCACHECHECK where DATE= ? and HOST= ?");
			query.setDate(0, bean.getDate());
			query.setString(1, bean.getHost());
			return query.uniqueResult();
		} catch (Exception e) {
			log.error("exist Bytes run into error.",e);
			return null;
		}finally{
			hibernate.closeSession(session);
		}
	}
	
	private QueryCacheNode initQueryCacheNode(int dbIndex) {
		Map<String,String> valueMap = getValuesMap().get(dbIndex);
		Map<String,String> variablesMap = getVariablesMap().get(dbIndex); 
		QueryCacheNode bean = new QueryCacheNode();
		bean.setDate(new Date(System.currentTimeMillis()));
		bean.setHost(perfUtil.getDBHostPort(dbIndex));
		
		double queryCacheSize = new Double(variablesMap.get("query_cache_size"));
		double queryCacheSizeFormat = new Double(perfUtil.doubleFormat("0.00", queryCacheSize /(1024*1024)));//单位MB
		bean.setQueryCacheSize(Math.round(queryCacheSize));
		bean.setQueryCacheSizeFormat(queryCacheSizeFormat);
		bean.setQueryCacheType(variablesMap.get("query_cache_type"));
		
		double queryCacheLimit = new Double(variablesMap.get("query_cache_limit"));
		double queryCacheLimitFormat = new Double(perfUtil.doubleFormat("0.00", queryCacheLimit /1024));//单位KB
		double queryCacheMinUnit = new Double(variablesMap.get("query_cache_min_res_unit"));
		double queryCacheMinUnitFormat = new Double(perfUtil.doubleFormat("0.00", queryCacheMinUnit /1024));//单位KB

		bean.setQueryCacheLimit(Math.round(queryCacheLimit));
		bean.setQueryCacheLimitFormat(queryCacheLimitFormat);
		bean.setQueryCacheMinUnit(Math.round(queryCacheMinUnit));
		bean.setQueryCacheMinUnitFormat(queryCacheMinUnitFormat);
		
		double comSelect = new Double(valueMap.get("Com_select"));
		bean.setComSelect(Math.round(comSelect));
		
		double qCacheFreeMemory = new Double(valueMap.get("Qcache_free_memory"));
		double qCacheFreeMemoryFormat = new Double(perfUtil.doubleFormat("0.00", qCacheFreeMemory /(1024*1024)));//单位MB
		double freeMemoryRate = new Double(perfUtil.doubleFormat("0.00", perfUtil.divide(qCacheFreeMemory,queryCacheSize) * 100));
		bean.setqCacheFreeMemory(Math.round(qCacheFreeMemory));
		bean.setqCacheFreeMemoryFormat(qCacheFreeMemoryFormat);
		bean.setFreeMemoryRate(freeMemoryRate);
		
		float qCacheTotalBlock = new Float(valueMap.get("Qcache_total_blocks"));
		float qCacheFreeBlock = new Float(valueMap.get("Qcache_free_blocks"));
		double freeBlockRate = new Double(perfUtil.doubleFormat("0.00", perfUtil.divide(qCacheFreeBlock,qCacheTotalBlock) * 100));
		bean.setqCacheTotalBlock(Math.round(qCacheTotalBlock));
		bean.setqCacheFreeBlock(Math.round(qCacheFreeBlock));
		bean.setFreeBlockRate(freeBlockRate);
		
		double qCacheHit = new Double(valueMap.get("Qcache_hits"));
		double cacheHitRate = new Double(perfUtil.doubleFormat("0.00", perfUtil.divide(qCacheHit,qCacheHit + comSelect) * 100));
		bean.setqCacheHit(Math.round(qCacheHit));
		bean.setCacheHitRate(cacheHitRate);
		
		double qCacheInsert = new Double(valueMap.get("Qcache_inserts"));
		double cacheInsertRate = new Double(perfUtil.doubleFormat("0.00", perfUtil.divide(qCacheInsert,comSelect) * 100));
		bean.setqCacheInsert(Math.round(qCacheInsert));
		bean.setCacheInsertRate(cacheInsertRate);
		
		double qCacheNoCached = new Double(valueMap.get("Qcache_not_cached"));
		double noCachedRate = new Double(perfUtil.doubleFormat("0.00", perfUtil.divide(qCacheNoCached,comSelect) * 100)); 
		bean.setqCacheNoCached(Math.round(qCacheNoCached));
		bean.setNoCachedRate(noCachedRate);
		
		bean.setqCacheLowmem(new Long(valueMap.get("Qcache_lowmem_prunes")));
		bean.setqCacheQueryInCache(new Integer(valueMap.get("Qcache_queries_in_cache")));
		Object ID = selectID(bean);
		bean.setId(ID == null ? 0 : new Integer(ID.toString()));
		
		return bean;
	}

	private void operateExcel(QueryCacheNode bean) throws Exception{
		Workbook workBook = null;
		WritableWorkbook writeWorkBook = null;
		try {
			workBook = Workbook.getWorkbook(new File(sourcefile));
			writeWorkBook = Workbook.createWorkbook(new File(sourcefile), workBook);
			WritableSheet wsheet = writeWorkBook.getSheet(bean.getHost());
			int startColumnId = perfUtil.getStartPositionColumnId(wsheet,perfUtil.getLastReviewDate());
			int startRowId = perfUtil.getStartPositionRowId(wsheet,bean.getTaskNumber());
			Label label_0 = new Label(startColumnId, startRowId, bean.getQueryCacheSizeFormat()+ "MB");
			Label label_1 = new Label(startColumnId, startRowId + 1, bean.getQueryCacheType());
			Label label_2 = new Label(startColumnId, startRowId + 2, bean.getQueryCacheLimitFormat() + "KB");
			Label label_3 = new Label(startColumnId, startRowId + 3, bean.getQueryCacheMinUnitFormat() + "KB");
			Label label_4 = new Label(startColumnId, startRowId + 4, bean.getComSelect()+ "次");
			Label label_5 = new Label(startColumnId, startRowId + 5, bean.getqCacheFreeMemoryFormat() + "MB");
			Label label_6 = new Label(startColumnId, startRowId + 6, bean.getFreeMemoryRate() + "%");
			Label label_7 = new Label(startColumnId, startRowId + 7, bean.getqCacheTotalBlock() + "个");
			Label label_8 = new Label(startColumnId, startRowId + 8, bean.getqCacheFreeBlock() + "个");
			Label label_9 = new Label(startColumnId, startRowId + 9, bean.getFreeBlockRate() + "%");
			Label label_10 = new Label(startColumnId, startRowId + 10, bean.getqCacheHit() + "次");
			Label label_11 = new Label(startColumnId, startRowId + 11, bean.getCacheHitRate() + "%");
			Label label_12 = new Label(startColumnId, startRowId + 12, bean.getqCacheInsert() + "次");
			Label label_13 = new Label(startColumnId, startRowId + 13, bean.getCacheInsertRate() + "%");
			Label label_14 = new Label(startColumnId, startRowId + 14, bean.getqCacheNoCached() + "次");
			Label label_15 = new Label(startColumnId, startRowId + 15, bean.getNoCachedRate() + "%");
			Label label_16 = new Label(startColumnId, startRowId + 16, bean.getqCacheLowmem() + "次");
			Label label_17 = new Label(startColumnId, startRowId + 17, bean.getqCacheQueryInCache() + "个");
			wsheet.addCell(label_0);wsheet.addCell(label_1);wsheet.addCell(label_2);wsheet.addCell(label_3);
			wsheet.addCell(label_4);wsheet.addCell(label_5);wsheet.addCell(label_6);wsheet.addCell(label_7);
			wsheet.addCell(label_8);wsheet.addCell(label_9);wsheet.addCell(label_10);wsheet.addCell(label_11);
			wsheet.addCell(label_12);wsheet.addCell(label_13);wsheet.addCell(label_14);wsheet.addCell(label_15);
			wsheet.addCell(label_16);wsheet.addCell(label_17);
			writeWorkBook.write();
		} catch (Exception e) {
			log.error("operate excel run into error", e);
			throw new Exception(e);
		}finally{
			try {
				writeWorkBook.close();workBook.close();
			} catch (Exception e) {
				log.error("close WorkBook run into error",e);
				throw new Exception(e);
			}
		}
	}
}
